package database

import (
	"strconv"
	"strings"
	"time"

	"gitee.com/sansaniot/ssiot-core/logger"
	"gorm.io/gorm"

	"ssdevice/common/utils"
	"ssdevice/internal/device/models"
)

func PartitionTb(db *gorm.DB, partType string) {
	tableNames := []string{models.SysHistorySensor{}.TableName(), models.SysHistorySystem{}.TableName()}
	for _, tableName := range tableNames {
		createTable(db, tableName, partType)
	}
}

func createTable(db *gorm.DB, tableName, partType string) {
	scan := map[string]interface{}{}
	if err := db.Raw("SELECT relkind FROM pg_class WHERE relname = ?", tableName).Scan(&scan).Error; err != nil {
		logger.Error(err)
		return
	}
	if len(scan) == 0 {
		return
	}
	// 已经是分区表
	if scan["relkind"] == "p" {
		return
	}
	logger.Info("【开始构建主分区表, 请耐心等待...】")
	// 默认按dev_sn分区
	sql1 := `CREATE TABLE "` + tableName + `_systmp" (
			  "dev_sn" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
			  "value" jsonb,
			  "update_time" timestamptz(6) NOT NULL,
              "date_time" varchar(10) COLLATE "pg_catalog"."default" NOT NULL
			) partition by list("dev_sn")`
	if partType == "date" {
		// 配置按天分区
		sql1 = `CREATE TABLE "` + tableName + `_systmp" (
			  "dev_sn" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
			  "value" jsonb,
			  "update_time" timestamptz(6) NOT NULL,
              "date_time" varchar(10) COLLATE "pg_catalog"."default" NOT NULL
			) partition by range("date_time")`
	}
	if err := db.Raw(sql1).Scan(&scan).Error; err != nil {
		logger.Error(err)
		return
	}
	logger.Info("【" + tableName + "备份为" + tableName + "_sysbackup】")
	sql2 := `alter table ` + tableName + ` rename to ` + tableName + `_sysbackup`
	if err := db.Raw(sql2).Scan(&scan).Error; err != nil {
		logger.Error(err)
		return
	}
	logger.Info("【" + tableName + "_systmp分区表重命名为" + tableName + "】")
	sql3 := `alter table ` + tableName + `_systmp rename to ` + tableName
	if err := db.Raw(sql3).Scan(&scan).Error; err != nil {
		logger.Error(err)
		return
	}
	// 必需同步
	logger.Info("【开始构建子分区表, 请耐心等待...】")
	total, err := partitionHisSensor(db, tableName, partType)
	if err != nil {
		logger.Error(err)
		return
	}
	logger.Infof("【分区表%v构建完成.】", tableName)
	if total == 0 {
		return
	}
}

func partitionHisSensor(db *gorm.DB, tableName, partType string) (total int64, err error) {
	scan := map[string]interface{}{}
	sqlc := `select count(*) from ` + tableName + `_sysbackup`
	if err = db.Raw(sqlc).Scan(&scan).Error; err != nil {
		return
	}
	total = scan["count"].(int64)

	switch partType {
	case "date":
		timeRs := make([]map[string]interface{}, 0)
		sql4 := `select DISTINCT(to_char(update_time,'YYYY-MM')) as datetime from ` + tableName + `_sysbackup`
		if err = db.Raw(sql4).Scan(&timeRs).Error; err != nil {
			return
		}
		for _, m := range timeRs {
			datetime := utils.GetStringValueFiled(m, "datetime")
			dts := strings.Split(datetime, "-")
			year, _ := strconv.Atoi(dts[0])
			mon, _ := strconv.Atoi(dts[1])
			start := time.Date(year, time.Month(mon), 1, 0, 0, 0, 0, time.Local)
			end := time.Date(year, time.Month(mon+1), 1, 0, 0, 0, 0, time.Local)
			for start.Before(end) {
				timeStr1 := start.Format("2006-01-02")
				start = start.AddDate(0, 0, 1)
				timeStr2 := start.Format("2006-01-02")
				subTableName := tableName + "_" + strings.ReplaceAll(strings.ToLower(timeStr1), "-", "_")
				if err = db.Raw("create table if not EXISTS " + subTableName + " partition of " + tableName + " for values from ('" + timeStr1 + "') to ('" + timeStr2 + "') ").Scan(&scan).Error; err != nil {
					return
				}
				db.Raw("drop index if EXISTS idx_" + subTableName + "_sn").Scan(&scan)
				cidx := `CREATE INDEX "idx_` + subTableName + `_sn" ON "` + subTableName + `" USING btree (
					"dev_sn" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
				);`
				if err = db.Raw(cidx).Scan(&scan).Error; err != nil {
					return
				}
			}
		}
	default:
		snRs := make([]map[string]interface{}, 0)
		sql4 := `select dev_sn from ` + tableName + `_sysbackup GROUP BY dev_sn`
		if err = db.Raw(sql4).Scan(&snRs).Error; err != nil {
			logger.Error(err)
			return
		}
		for _, m := range snRs {
			sn := utils.GetStringValueFiled(m, "dev_sn")
			snn := strings.ReplaceAll(strings.ReplaceAll(strings.ToLower(sn), "-", "_"), " ", "_")
			subTableName := tableName + "_" + snn
			if err = db.Raw("create table if not EXISTS " + subTableName + " partition of " + tableName + " for values in ('" + sn + "') ").Scan(&scan).Error; err != nil {
				logger.Error(err)
				return
			}
			db.Raw("drop index if EXISTS idx_" + subTableName + "_datetime").Scan(&scan)
			cidx := `CREATE INDEX "idx_` + subTableName + `_datetime" ON "` + subTableName + `" USING btree (
					"date_time" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
				);`
			if err = db.Raw(cidx).Scan(&scan).Error; err != nil {
				logger.Error(err)
				return
			}
		}
	}
	return
}
